package exames;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import medicos.Medico;
import medicos.MedicoNaoEncontradoException;
import medicos.RepositorioMedicos;
import medicos.RepositorioMedicosBDR;
import pacientes.Paciente;
import pacientes.PacienteNaoEncontradoException;
import pacientes.RepositorioPacientes;
import pacientes.RepositorioPacientesBDR;
import util.Data;
import util.PersistenceMechanismException;
import util.PersistenceMechanismRDBMS;
import util.RepositorioException;

public class RepositorioExamesBDR implements RepositorioExames{
	
	private static RepositorioExamesBDR instance;
	private PersistenceMechanismRDBMS pm;
	
	private RepositorioExamesBDR() {
        try {
            pm = PersistenceMechanismRDBMS.getInstance();
            pm.connect(this);
        }
        catch (Exception ex) {
            //System.out.println(ex.getMessage()); 
            ex.printStackTrace();
        }        
    }

    public synchronized static RepositorioExamesBDR getInstance() {
        if (instance == null) {
            instance = new RepositorioExamesBDR();
        }
        return instance;
    }
    
	public void inserir(Exame exame) throws RepositorioException {
        try {
            Statement statement = (Statement) pm.getCommunicationChannel(this);
            String sql = "INSERT INTO EXAMES VALUES ('" +
            exame.getCodigo() + "', '" +
            exame.getMedico().getCodigo() + "', '" +
            exame.getPaciente().getCodigo() + "', '" +
            exame.getDescricao() + "', '" +
            exame.getResultado() + "', '" +
            exame.getDataCadastro().toString() + "')";
            System.out.println(sql);
            statement.executeUpdate(sql);
        } catch (PersistenceMechanismException ex) {
			throw new RepositorioException(ex);
        } catch (SQLException e) {
            throw new RepositorioException(e);
        } finally {
        	try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
        }
    }
	
	public void remover(long codigo) throws ExameNaoEncontradoException, RepositorioException {
        try{
            Statement statement = (Statement) pm.getCommunicationChannel(this);
		    int i = statement.executeUpdate("DELETE FROM EXAMES WHERE codigo='" + codigo + "'");
            if (i == 0) {
            	throw new ExameNaoEncontradoException(codigo);
            }
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
		} catch(SQLException e){
            throw new RepositorioException(e);            
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}
	public RepositorioExames procurar(Exame exame) throws RepositorioException{
		RepositorioExames resposta = new RepositorioExamesLista();
        try {
            Statement statement = (Statement) pm.getCommunicationChannel(this);
            String sql = "SELECT * FROM EXAMES ";
            boolean where = false;
            if (exame.getCodigo()>0) {
            	where = true;
            	sql += " where codigo='"+exame.getCodigo()+"' ";
            }
            if (exame.getMedico()!= null){
            	if (! where) {
            		where = true;
            		sql+=" where fk_medico = '";
            	} else {
            		sql += "and fk_medico = '";
            	}
            	sql += exame.getMedico().getCodigo()+"' ";
            }
            if (exame.getPaciente()!= null){
            	if (! where) {
            		where = true;
            		sql+=" where fk_paciente = '";
            	} else {
            		sql += "and fk_paciente = '";
            	}
            	sql+=exame.getPaciente().getCodigo()+"' ";
            }
            if (exame.getDataCadastro()!= null){
            	if (! where) {
            		where = true;
            		sql+=" where dataCadastro = '";
            	} else {
            		sql += "and dataCadastro = '";
            	}
            	sql+=exame.getDataCadastro().toString()+"' ";
            }
            if (exame.getResultado()!= null){
            	if (! where) {
            		where = true;
            		sql+=" where resultado like '%";
            	} else {
            		sql += "and resultado = '%";
            	}
            	sql+=exame.getResultado()+"%' ";
            }
            if (exame.getDescricao()!= null){
            	if (! where) {
            		where = true;
            		sql+=" where descricao like '%";
            	} else {
            		sql += "and descricao = '%";
            	}
            	sql+=exame.getDescricao()+"%' ";
            }            
            System.out.println("==========   SQL = "+sql);
            ResultSet resultset = statement.executeQuery(sql);
            while (resultset.next()) {
            	RepositorioMedicos rm = RepositorioMedicosBDR.getInstance();
            	Medico medico;
				try {
					medico = rm.procurar(resultset.getLong("fk_medico"));
				} catch (MedicoNaoEncontradoException e) {
					medico = null;
				}
            	RepositorioPacientes rp = RepositorioPacientesBDR.getInstance();
            	Paciente paciente;
				try {
					paciente = rp.procurar(resultset.getLong("fk_paciente"));
				} catch (PacienteNaoEncontradoException e) {
					paciente = null;
				}
				Exame e = new Exame(resultset.getLong("codigo"),
         				  medico,
           				  paciente,
           		          resultset.getString("descricao"), 
           		          resultset.getString("resultado"),
           		          new Data(resultset.getString("data")));
                resposta.inserir(e);                
            }
			resultset.close();
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
        } catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		
        return resposta;
    }
	public Exame procurar(long codigo) throws ExameNaoEncontradoException, RepositorioException {
   	    Exame exame = null;
   	    try {
            Statement statement = (Statement) pm.getCommunicationChannel(this);
            ResultSet resultset = statement.executeQuery("SELECT * FROM EXAMES WHERE codigo = '" + codigo + "'");
            if (resultset.next()) {
            	RepositorioMedicos rm = RepositorioMedicosBDR.getInstance();
            	Medico medico;
				try {
					medico = rm.procurar(resultset.getLong("fk_medico"));
				} catch (MedicoNaoEncontradoException e) {
					medico = null;
				}
            	RepositorioPacientes rp = RepositorioPacientesBDR.getInstance();
            	Paciente paciente;
				try {
					paciente = rp.procurar(resultset.getLong("fk_paciente"));
				} catch (PacienteNaoEncontradoException e) {
					paciente = null;
				}
				exame = new Exame(resultset.getLong("codigo"),
						  medico,
						  paciente,
					      resultset.getString("descricao"), 
					      resultset.getString("resultado"),
					      new Data(resultset.getString("data")));				
            } else {
            	throw new ExameNaoEncontradoException(codigo);
            }
			resultset.close();
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
        } catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
		return exame;
	}
	public void atualizar(Exame exame) throws ExameNaoEncontradoException, RepositorioException {
	    try {
    	    Statement statement = (Statement) pm.getCommunicationChannel(this);
            int i = statement.executeUpdate("UPDATE EXAMES SET  fk_medico = '"+exame.getMedico().getCodigo()+
            											 "', fk_paciente = '"+exame.getPaciente().getCodigo()+
            											 "', descricao = '"+exame.getDescricao()+
            											 "', resultado = '"+exame.getResultado()+
            											 "', data = '"+exame.getDataCadastro()+" 'WHERE codigo = '" + exame.getCodigo() + "'");
            if (i == 0) {
            	throw new ExameNaoEncontradoException(exame.getCodigo());
            }
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
		} catch (SQLException e) {
		    throw new RepositorioException(e);
	    } finally {
	    	try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
	}
	public boolean existe(long codigo) throws RepositorioException {
        boolean resposta = false;
        try {
            Statement statement = (Statement) pm.getCommunicationChannel(this);
            ResultSet resultset = statement.executeQuery("SELECT * FROM EXAMES WHERE codigo = '" + codigo + "'");
            resposta = resultset.next();
			resultset.close();
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
        } catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}
        return resposta;
    }
	
	public RepositorioExames getExames() throws RepositorioException {
        RepositorioExames resposta = new RepositorioExamesLista();
        try {
            Statement statement = (Statement) pm.getCommunicationChannel(this);
            ResultSet resultset = statement.executeQuery("SELECT * FROM EXAMES");
            while (resultset.next()) {
            	RepositorioMedicos rm = RepositorioMedicosBDR.getInstance();
            	Medico medico;
				try {
					medico = rm.procurar(resultset.getLong("fk_medico"));
				} catch (MedicoNaoEncontradoException e) {
					medico = null;
				}
            	RepositorioPacientes rp = RepositorioPacientesBDR.getInstance();
            	Paciente paciente;
				try {
					paciente = rp.procurar(resultset.getLong("fk_paciente"));
				} catch (PacienteNaoEncontradoException e) {
					paciente = null;
				}
				Exame exame= new Exame(resultset.getLong("codigo"),
         				  medico,
           				  paciente,
           		          resultset.getString("descricao"), 
           		          resultset.getString("resultado"),
           		          new Data(resultset.getString("data")));
                resposta.inserir(exame);
            }
			resultset.close();
		} catch(PersistenceMechanismException e){
            throw new RepositorioException(e);
        } catch (SQLException e) {
			throw new RepositorioException(e);
		} finally {
			try {
				pm.releaseCommunicationChannel();
			} catch (PersistenceMechanismException ex) {
				throw new RepositorioException(ex);
			}
		}		
        return resposta;
    }
	public IteratorExames getIterator() throws RepositorioException {
        return this.getExames().getIterator();
    }

}
